
package modele;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import modele.exceptions.EchantillonInexistantException;

/**
 *
 * @author Danicela
 */
public class Echantillon
{


    private int id;
    private String lieu;
    private String identifiant;
    private String statut;
    private Date date;

    public Echantillon(int id, String lieu, String identifiant, String statut, Date date)
    {
        this.id = id;
        this.lieu = lieu;
        this.identifiant = identifiant;
        this.statut = statut;
        this.date = date;
    }

    public int getId()
    {
        return id;
    }

    public Date getDate() {
        return date;
    }

    public String getIdentifiant() {
        return identifiant;
    }

    public String getLieu() {
        return lieu;
    }

    public String getStatut() {
        return statut;
    }

    //Accès BD

    public static List<Echantillon> getTousLesEchantillons() throws SQLException
    {
        List<Echantillon> resultat = new ArrayList<Echantillon>();

        String requete = "Select id_echantillon, lieu, identifiant, statut, date from echantillon order by date;";
        Connection c = Database.getConnection();
        Statement s = c.createStatement();
        ResultSet rs = s.executeQuery(requete);

        while(rs.next())
        {
            int idEchantillon = rs.getInt(1);
            String lieu = rs.getString(2);
            String identifiant = rs.getString(3);
            String statut = rs.getString(4);
            Date date = rs.getDate(5);
            Echantillon e = new Echantillon(idEchantillon, lieu, identifiant, statut, date);

            resultat.add(e);
        }

        c.close();

        return resultat;
    }
   
    public static Echantillon getEchantillon(int idEchantillon) throws EchantillonInexistantException, SQLException
    {
        Echantillon resultat = null;

        String requete = "Select lieu, identifiant, statut, date from echantillon where id_echantillon = ?;";
        Connection c = Database.getConnection();
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setInt(1, idEchantillon);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
        {
            //L'échantillon existe
            String lieu = rs.getString(1);
            String identifiant = rs.getString(2);
            String statut = rs.getString(3);
            Date date = rs.getDate(4);
            resultat = new Echantillon(idEchantillon, lieu, identifiant, statut, date);
        }
        else
        {
            //L'échantillon n'existe pas
            throw new EchantillonInexistantException();
        }

        c.close();

        return resultat;

    }

    public static void enregistrerNouvelEchantillon(String lieu, String identifiant, String statut, Date date) throws SQLException
    {
        String requete = "Insert into echantillon(lieu, identifiant, statut, date) values(?, ?, ?, ?);";
        Connection c = Database.getConnection();
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setString(1, lieu);
        ps.setString(2, identifiant);
        ps.setString(3, statut);
        ps.setDate(4, new java.sql.Date(date.getTime()));

        ps.executeUpdate();

        c.close();
    }

    public static List<Resultat> getResultats(int idEchantillon) throws EchantillonInexistantException, SQLException
    {
        List<Resultat> resultat = new ArrayList<Resultat>();

        String requete = "Select e.lieu, e.identifiant, e.statut, e.date, a.id_analyse, a.identifiant, a.commentaire, r.id_resultat, r.date, r.texte from echantillon e, analysetable a, resultat r where e.id_echantillon = ? and e.id_echantillon = r.id_echantillon and a.id_analyse = r.id_analyse order by r.date;";
        Connection c = Database.getConnection();
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setInt(1, idEchantillon);
        ResultSet rs = ps.executeQuery();

        while(rs.next())
        {
            String lieu = rs.getString(1);
            String identifiant = rs.getString(2);
            String statut = rs.getString(3);
            Date date = rs.getDate(4);
            Echantillon e = new Echantillon(idEchantillon, lieu, identifiant, statut, date);
            
            int idAnalyse = rs.getInt(5);
            String identifiantAnalyse = rs.getString(6);
            String commentaire = rs.getString(7);
            Analyse a = new Analyse(idAnalyse, identifiantAnalyse, commentaire);
            
            int idResultat = rs.getInt(8);
            Date dateResultat = rs.getDate(9);
            String texte = rs.getString(10);
            Resultat r = new Resultat(idResultat, dateResultat, texte, e, a);

            resultat.add(r);
        }

        c.close();

        return resultat;
    }

    public static void supprimerEchantillon(int idEchantillon) throws EchantillonInexistantException, SQLException
    {
        String requete = "Delete FROM resultat WHERE id_echantillon = ?";
        Connection c = Database.getConnection();
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setInt(1, idEchantillon);
        ps.executeUpdate();

        requete = "Delete FROM echantillon WHERE id_echantillon = ?";
        ps = c.prepareStatement(requete);
        ps.setInt(1, idEchantillon);
        ps.executeUpdate();

        c.close();
    }

}
